/**
 * @swagger
 * /api/asset/status-analysis:
 *   get:
 *     tags:
 *       - 资产管理
 *     summary: 获取资产状态分析数据
 *     description: 分析资产的使用状态、维护情况和生命周期
 *     parameters:
 *       - in: query
 *         name: startDate
 *         schema:
 *           type: string
 *           format: date
 *         description: 开始日期 (YYYY-MM-DD)
 *       - in: query
 *         name: endDate
 *         schema:
 *           type: string
 *           format: date
 *         description: 结束日期 (YYYY-MM-DD)
 *     responses:
 *       200:
 *         description: 成功获取状态分析数据
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 code:
                   type: integer
                   example: 200
                   description: 响应状态码
                 message:
                   type: string
                   example: "状态分析获取成功"
                   description: 响应消息
 *                 data:
 *                   type: object
 *                   properties:
 *                     overview:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 统计概览标题
 *                         statistics:
 *                           type: object
 *                           properties:
 *                             totalAssets:
 *                               type: integer
 *                               description: 总资产数
 *                             assetsWithStatus:
 *                               type: integer
 *                               description: 有状态信息的资产数
 *                             distinctStatusCount:
 *                               type: integer
 *                               description: 不同状态类型数
 *                             statusRatio:
 *                               type: string
 *                               description: 状态完整率(%)
 *                     statusStatistics:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 各状态统计标题
 *                         statuses:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               assetStatus:
 *                                 type: string
 *                                 description: 资产状态
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                               totalFiscalFunding:
 *                                 type: string
 *                                 description: 财政拨款总额
 *                               avgValue:
 *                                 type: string
 *                                 description: 平均价值
 *                               totalBuildingArea:
 *                                 type: string
 *                                 description: 总建筑面积
 *                               totalLandArea:
 *                                 type: string
 *                                 description: 总土地面积
 *                               uniqueKeepers:
 *                                 type: integer
 *                                 description: 唯一领用人数
 *                               percentage:
 *                                 type: string
 *                                 description: 占比(%)
 *                     statusCategoryAnalysis:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 状态与分类交叉分析标题
 *                         combinations:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               assetStatus:
 *                                 type: string
 *                                 description: 资产状态
 *                               categoryName:
 *                                 type: string
 *                                 description: 分类名称
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                               avgValue:
 *                                 type: string
 *                                 description: 平均价值
 *                     statusValueRangeAnalysis:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 状态与价值区间分析标题
 *                         ranges:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               assetStatus:
 *                                 type: string
 *                                 description: 资产状态
 *                               valueRange:
 *                                 type: string
 *                                 description: 价值区间
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                     statusTimeRelation:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 状态与时间关系分析标题
 *                         timeStats:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               assetStatus:
 *                                 type: string
 *                                 description: 资产状态
 *                               totalAssets:
 *                                 type: integer
 *                                 description: 总资产数
 *                               assetsWithDate:
 *                                 type: integer
 *                                 description: 有日期的资产数
 *                               dateRatio:
 *                                 type: string
 *                                 description: 日期完整率(%)
 *                               earliestYear:
 *                                 type: string
 *                                 description: 最早年份
 *                               latestYear:
 *                                 type: string
 *                                 description: 最晚年份
 *                               recentAssets:
 *                                 type: integer
 *                                 description: 近期资产数(2020年后)
 *                               recentRatio:
 *                                 type: string
 *                                 description: 近期资产比例(%)
 *                     concerningStatuses:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 重点关注状态分析标题
 *                         statuses:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               assetStatus:
 *                                 type: string
 *                                 description: 资产状态
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                               avgValue:
 *                                 type: string
 *                                 description: 平均价值
 *                               highValueAssets:
 *                                 type: integer
 *                                 description: 高价值资产数(>5万)
 *                               highValueTotal:
 *                                 type: string
 *                                 description: 高价值资产总值
 *                               highValueRatio:
 *                                 type: string
 *                                 description: 高价值资产比例(%)
 *                     statusCompleteness:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 状态数据完整性分析标题
 *                         completeness:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               assetStatus:
 *                                 type: string
 *                                 description: 资产状态
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                               hasKeeper:
 *                                 type: integer
 *                                 description: 有领用人的资产数
 *                               hasPrice:
 *                                 type: integer
 *                                 description: 有价格的资产数
 *                               hasDate:
 *                                 type: integer
 *                                 description: 有日期的资产数
 *                               hasArea:
 *                                 type: integer
 *                                 description: 有面积的资产数
 *                               keeperRatio:
 *                                 type: string
 *                                 description: 领用人完整率(%)
 *                               priceRatio:
 *                                 type: string
 *                                 description: 价格完整率(%)
 *                               dateRatio:
 *                                 type: string
 *                                 description: 日期完整率(%)
 *                               areaRatio:
 *                                 type: string
 *                                 description: 面积完整率(%)
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *       500:
 *         description: 服务器内部错误
 */
import { withCors } from '../../../lib/cors';
import { success, error as errorResponse, handleResponse } from '../../../lib/response';
import { query } from '../../../lib/db';

/**
 * 处理资产状态分析请求
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 */
async function handler(req, res) {
  try {
    const { startDate, endDate } = req.query;

    let whereClause = '';
    const params = [];

    if (startDate && endDate) {
      whereClause = 'WHERE PURCHASE_DATE BETWEEN ? AND ?';
      params.push(startDate, endDate);
    } else if (startDate) {
      whereClause = 'WHERE PURCHASE_DATE >= ?';
      params.push(startDate);
    } else if (endDate) {
      whereClause = 'WHERE PURCHASE_DATE <= ?';
      params.push(endDate);
    }
    
    // 资产现状基础统计
    const statusBasicQuery = `
      SELECT 
        COUNT(*) as totalAssets,
        COUNT(CASE WHEN STATE IS NOT NULL AND STATE != '' THEN 1 END) as assetsWithStatus,
        COUNT(DISTINCT STATE) as distinctStatusCount
      FROM wd_sec_assets12
      ${whereClause}
    `;

    const statusBasicResult = await query(statusBasicQuery, params);
    const statusBasicData = statusBasicResult[0];

    // 各种现状的资产统计
    const statusStatsQuery = `
      SELECT 
        COALESCE(STATE, '未知状态') as assetStatus,
        COUNT(*) as assetCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue,
        SUM(COALESCE(CAIZXJFLY, 0)) as totalFiscalFunding,
        AVG(COALESCE(STOCK_PRICE, 0)) as avgValue,
        SUM(COALESCE(CHEKMJ, 0)) as totalBuildingArea,
        SUM(COALESCE(FENTMJ, 0)) as totalLandArea,
        COUNT(DISTINCT BASE_TEACHER_NAME_KEEPER) as uniqueKeepers
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY STATE
      ORDER BY assetCount DESC
    `;

    const statusStatsResult = await query(statusStatsQuery, params);

    // 现状与分类交叉分析
    const statusCategoryQuery = `
      SELECT 
        COALESCE(STATE, '未知状态') as assetStatus,
        COALESCE(ASSETS_SORT_GGJ_NAME, '未分类') as categoryName,
        COUNT(*) as assetCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue,
        AVG(COALESCE(STOCK_PRICE, 0)) as avgValue
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY STATE, ASSETS_SORT_GGJ_NAME
      HAVING assetCount > 0
      ORDER BY assetCount DESC
      LIMIT 30
    `;

    const statusCategoryResult = await query(statusCategoryQuery, params);

    // 现状与价值区间分析
    const statusValueRangeQuery = `
      SELECT 
        COALESCE(STATE, '未知状态') as assetStatus,
        CASE 
          WHEN COALESCE(STOCK_PRICE, 0) = 0 THEN '无价格'
          WHEN STOCK_PRICE <= 5000 THEN '0-5千'
          WHEN STOCK_PRICE <= 20000 THEN '5千-2万'
          WHEN STOCK_PRICE <= 50000 THEN '2万-5万'
          WHEN STOCK_PRICE <= 100000 THEN '5万-10万'
          ELSE '10万以上'
        END as valueRange,
        COUNT(*) as assetCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY STATE, 
        CASE 
          WHEN COALESCE(STOCK_PRICE, 0) = 0 THEN '无价格'
          WHEN STOCK_PRICE <= 5000 THEN '0-5千'
          WHEN STOCK_PRICE <= 20000 THEN '5千-2万'
          WHEN STOCK_PRICE <= 50000 THEN '2万-5万'
          WHEN STOCK_PRICE <= 100000 THEN '5万-10万'
          ELSE '10万以上'
        END
      HAVING assetCount > 0
      ORDER BY assetStatus, 
        CASE 
          WHEN valueRange = '无价格' THEN 0
          WHEN valueRange = '0-5千' THEN 1
          WHEN valueRange = '5千-2万' THEN 2
          WHEN valueRange = '2万-5万' THEN 3
          WHEN valueRange = '5万-10万' THEN 4
          ELSE 5
        END
    `;

    const statusValueRangeResult = await query(statusValueRangeQuery, params);

    // 现状与时间关系分析
    const statusTimeQuery = `
      SELECT 
        COALESCE(STATE, '未知状态') as assetStatus,
        COUNT(*) as totalAssets,
        COUNT(CASE WHEN PURCHASE_DATE IS NOT NULL AND PURCHASE_DATE != '' THEN 1 END) as assetsWithDate,
        MIN(CASE WHEN PURCHASE_DATE IS NOT NULL AND PURCHASE_DATE != '' THEN SUBSTRING(PURCHASE_DATE, 1, 4) END) as earliestYear,
        MAX(CASE WHEN PURCHASE_DATE IS NOT NULL AND PURCHASE_DATE != '' THEN SUBSTRING(PURCHASE_DATE, 1, 4) END) as latestYear,
        COUNT(CASE WHEN PURCHASE_DATE IS NOT NULL AND PURCHASE_DATE != '' AND SUBSTRING(PURCHASE_DATE, 1, 4) >= '2020' THEN 1 END) as recentAssets
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY STATE
      HAVING totalAssets > 0
      ORDER BY totalAssets DESC
    `;

    const statusTimeResult = await query(statusTimeQuery, params);

    // 需要关注的现状分析
    const concernStatusQuery = `
      SELECT 
        COALESCE(STATE, '未知状态') as assetStatus,
        COUNT(*) as assetCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue,
        AVG(COALESCE(STOCK_PRICE, 0)) as avgValue,
        COUNT(CASE WHEN STOCK_PRICE > 50000 THEN 1 END) as highValueAssets,
        SUM(CASE WHEN STOCK_PRICE > 50000 THEN COALESCE(STOCK_PRICE, 0) END) as highValueTotal
      FROM wd_sec_assets12
      WHERE STATE IS NOT NULL AND STATE != '' ${whereClause ? 'AND ' + whereClause.replace('WHERE ', '') : ''}
      GROUP BY STATE
      HAVING assetCount > 0
      ORDER BY totalValue DESC
    `;

    const concernStatusResult = await query(concernStatusQuery, params);

    // 现状完整性分析
    const statusCompletenessQuery = `
      SELECT 
        COALESCE(STATE, '未知状态') as assetStatus,
        COUNT(*) as assetCount,
        COUNT(CASE WHEN BASE_TEACHER_NAME_KEEPER IS NOT NULL AND BASE_TEACHER_NAME_KEEPER != '' THEN 1 END) as hasKeeper,
        COUNT(CASE WHEN STOCK_PRICE > 0 THEN 1 END) as hasPrice,
        COUNT(CASE WHEN PURCHASE_DATE IS NOT NULL AND PURCHASE_DATE != '' THEN 1 END) as hasDate,
        COUNT(CASE WHEN CHEKMJ > 0 THEN 1 END) as hasArea,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY STATE
      ORDER BY assetCount DESC
    `;

    const statusCompletenessResult = await query(statusCompletenessQuery, params);

    const responseData = {
      overview: {
        title: "资产现状统计概览",
        statistics: {
          totalAssets: parseInt(statusBasicData.totalAssets),
          assetsWithStatus: parseInt(statusBasicData.assetsWithStatus || 0),
          distinctStatusCount: parseInt(statusBasicData.distinctStatusCount || 0),
          statusRatio: ((parseInt(statusBasicData.assetsWithStatus || 0) / parseInt(statusBasicData.totalAssets)) * 100).toFixed(2)
        }
      },
      statusStatistics: {
        title: "各现状资产统计",
        statuses: statusStatsResult.map(row => ({
          assetStatus: row.assetStatus,
          assetCount: parseInt(row.assetCount),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          totalFiscalFunding: parseFloat(row.totalFiscalFunding || 0).toFixed(2),
          avgValue: parseFloat(row.avgValue || 0).toFixed(2),
          totalBuildingArea: parseFloat(row.totalBuildingArea || 0).toFixed(2),
          totalLandArea: parseFloat(row.totalLandArea || 0).toFixed(2),
          uniqueKeepers: parseInt(row.uniqueKeepers || 0),
          percentage: ((parseInt(row.assetCount) / parseInt(statusBasicData.totalAssets)) * 100).toFixed(2)
        }))
      },
      statusCategoryAnalysis: {
        title: "现状与分类交叉分析(Top30)",
        combinations: statusCategoryResult.map(row => ({
          assetStatus: row.assetStatus,
          categoryName: row.categoryName,
          assetCount: parseInt(row.assetCount),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          avgValue: parseFloat(row.avgValue || 0).toFixed(2)
        }))
      },
      statusValueRangeAnalysis: {
        title: "现状与价值区间分析",
        ranges: statusValueRangeResult.map(row => ({
          assetStatus: row.assetStatus,
          valueRange: row.valueRange,
          assetCount: parseInt(row.assetCount),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2)
        }))
      },
      statusTimeRelation: {
        title: "现状与时间关系分析",
        timeStats: statusTimeResult.map(row => ({
          assetStatus: row.assetStatus,
          totalAssets: parseInt(row.totalAssets),
          assetsWithDate: parseInt(row.assetsWithDate || 0),
          dateRatio: ((parseInt(row.assetsWithDate || 0) / parseInt(row.totalAssets)) * 100).toFixed(2),
          earliestYear: row.earliestYear || '未知',
          latestYear: row.latestYear || '未知',
          recentAssets: parseInt(row.recentAssets || 0),
          recentRatio: ((parseInt(row.recentAssets || 0) / parseInt(row.totalAssets)) * 100).toFixed(2)
        }))
      },
      concerningStatuses: {
        title: "重点关注现状分析",
        statuses: concernStatusResult.map(row => ({
          assetStatus: row.assetStatus,
          assetCount: parseInt(row.assetCount),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          avgValue: parseFloat(row.avgValue || 0).toFixed(2),
          highValueAssets: parseInt(row.highValueAssets || 0),
          highValueTotal: parseFloat(row.highValueTotal || 0).toFixed(2),
          highValueRatio: ((parseInt(row.highValueAssets || 0) / parseInt(row.assetCount)) * 100).toFixed(2)
        }))
      },
      statusCompleteness: {
        title: "现状数据完整性分析",
        completeness: statusCompletenessResult.map(row => ({
          assetStatus: row.assetStatus,
          assetCount: parseInt(row.assetCount),
          hasKeeper: parseInt(row.hasKeeper || 0),
          hasPrice: parseInt(row.hasPrice || 0),
          hasDate: parseInt(row.hasDate || 0),
          hasArea: parseInt(row.hasArea || 0),
          keeperRatio: ((parseInt(row.hasKeeper || 0) / parseInt(row.assetCount)) * 100).toFixed(2),
          priceRatio: ((parseInt(row.hasPrice || 0) / parseInt(row.assetCount)) * 100).toFixed(2),
          dateRatio: ((parseInt(row.hasDate || 0) / parseInt(row.assetCount)) * 100).toFixed(2),
          areaRatio: ((parseInt(row.hasArea || 0) / parseInt(row.assetCount)) * 100).toFixed(2),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2)
        }))
      }
    };

    return handleResponse(res, success(responseData, "资产现状分析获取成功"));
    
  } catch (error) {
    console.error('Asset status analysis error:', error);
    return handleResponse(res, errorResponse("获取资产现状分析失败", 500));
  }
}

export default withCors(handler, ['GET']);